package com.ww.springboot.boot.util;


import com.ww.springboot.boot.util.dto.XlsInvoiceDTO;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述：转订单请求实体
 *
 * @author wanwei
 * @since 2021-05-27 14:26
 */
public class TestXlsInvoice {

	public static void main(String[] args) throws FileNotFoundException {
		OutputStream output = new FileOutputStream("C:\\Users\\ZH500\\桌面\\test\\test.xls");

		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("ceshi");
		//设置style --标题
		CellStyle cellStyleTitle = getTitleStyle(wb);
		//设置style --通用
		CellStyle cellStyleCommon = getCommonStyle(wb);
		//设置列宽
		sheet.setColumnWidth(0, 25 * 256);
		sheet.setColumnWidth(1, 18 * 256);
		sheet.setColumnWidth(2, 15 * 256);
		sheet.setColumnWidth(4, 15 * 256);

		//获取excel数据
		List<XlsInvoiceDTO> xlsInvoiceDTOS = getExcelData(sheet, cellStyleTitle, cellStyleCommon);

		//构建excel
		bulidExcel(sheet, xlsInvoiceDTOS);

		try {
			output.flush();
			wb.write(output);
			//output.close();
		} catch (IOException e) {
			e.printStackTrace();
			System.out.println("Output is closed");
		}
	}

	private static void bulidExcel(HSSFSheet sheet, List<XlsInvoiceDTO> xlsInvoiceDTOS) {
		int index = -1;
		HSSFRow row = null;
		for (XlsInvoiceDTO dto : xlsInvoiceDTOS) {
			CellRangeAddress region = null;
			if (dto.getIsMerge()) {
				 region = new CellRangeAddress(dto.getY1(), dto.getY2(), dto.getX1(), dto.getX2());
			}
			if (index != dto.getY1()) {
				row = sheet.createRow(dto.getY1());
				if(dto.getY1() == 27){
					row.setHeightInPoints(30);
				}else{
					row.setHeightInPoints(15);
				}
				index = dto.getY1();
			}
			HSSFCell cell = row.createCell(dto.getX1());
			cell.setCellStyle(dto.getStyle());
			cell.setCellValue(dto.getText().toString());

			if(dto.getIsMerge()){
				borderCellRange(region, sheet);
				sheet.addMergedRegion(region);
			}
		}
	}

	private static List<XlsInvoiceDTO> getExcelData(HSSFSheet sheet, CellStyle cellStyleTitle, CellStyle cellStyleCommon) {
		List<XlsInvoiceDTO> xlsInvoiceDTOS = new ArrayList<>();
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(0, 2, 0, 4, "invoice", cellStyleTitle));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(3, 4, 0, 1, "Shipper寄件人:", cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(3, 4, 2, 3, "Air waybill Number单号:", cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(3, 4, 4, 4,"xxxxx",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,5, 5, 0, 0,"Company Name公司名称:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(5, 5, 1, 4,"Shenzhen Zehui Technology Co.,ltd",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(6, 8, 0, 0,"Address地址:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(6, 8, 1, 4,"No.1 shuitian first road, tongle community, longgang street, longgang district, shenzhen,China 518116",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,9, 9, 0, 0,"Contact Person联系人:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(9, 9, 1, 4,"Anny",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,10, 10, 0, 0,"Phone/Fax:电话/传真：",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(10, 10, 1, 4,"755-84525122-899",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(11, 11, 0, 4,"",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(12, 13, 0, 4,"Consignee收件人：株式会社　ドリームラボ",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,14, 14, 0, 0,"Company Name公司名称:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(14, 14, 1, 4,"",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(15, 17, 0, 0,"Address地址:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(15, 17, 1, 4,"530-0028;1F Naniwa building west  4-12 Banzaicho",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,18, 18, 0, 0,"Contact Person联系人:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(18, 18, 1, 4,"株式会社　ドリームラボ",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(19, 21, 0, 0,"Phone/Fax电话/传真:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(19, 21, 1, 4,"8061958365",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(22, 23, 0, 0,"City城市名:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(22, 23, 1, 1,"Osaka shi kita ku",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(22, 23, 2, 2,"Postal Code邮编:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(22, 23, 3, 4,"5300028",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(24, 25, 0, 0,"State/Country国家名:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(24, 25, 1, 1,"Osaka fu/Japan",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(24, 25, 2, 2,"Total Weight重量:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(24, 25, 3, 4,"6.00(KG)",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(26, 26, 0, 4,"",cellStyleCommon));

		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,27, 27, 0, 0,"货 物 名 称",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,27, 27, 1, 1,"数 量",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,27, 27, 2, 2,"(USD)单价",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,27, 27, 3, 3,"(USD)总价",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,27, 27, 4, 4,"",cellStyleCommon));

		int y = 28;

		for(int i = 0;i<3;i++){
			xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 0, 0,"milk cup",cellStyleCommon));
			xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 1, 1,"33",cellStyleCommon));
			xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 2, 2,"1.50",cellStyleCommon));
			xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 3, 3,"49.5",cellStyleCommon));
			xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 4, 4,"",cellStyleCommon));
			y++;
		}
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(y, y, 0, 4,"",cellStyleCommon));
		y++;
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 0, 0,"tatal",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 1, 1,"33",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 2, 2,"1.50",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 3, 3,"$49.5",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 4, 4,"",cellStyleCommon));
		y++;
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(false,y, y, 0, 0,"Total Invoice Value总价:",cellStyleCommon));
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(y, y, 1, 4,"FORTY-NINE DOLLARS AND FIFTY CENTS ONLY",cellStyleCommon));
		y++;
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(y, y, 0, 4,"",cellStyleCommon));
		y++;
		xlsInvoiceDTOS.add(new XlsInvoiceDTO(y, y=y+2, 0, 4,"I declare that the above information is true and correct to the best of my knowledge,And that the goods are origin:CHINA \n 本人认为以上提供的资料属实和正确，货物原产地是：中国",cellStyleCommon));

		for(int i = 0;i<=y;i++){
			HSSFRow row = sheet.createRow(i);
			row.setHeightInPoints(15);
		}
		return xlsInvoiceDTOS;
	}

	private static CellStyle getCommonStyle(HSSFWorkbook wb) {
		CellStyle cellStyleCommon = wb.createCellStyle();                        //表头单元格样式
		cellStyleCommon.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyleCommon.setBorderLeft(BorderStyle.THIN); // 左边框
		cellStyleCommon.setBorderRight(BorderStyle.THIN); // 有边框
		cellStyleCommon.setBorderTop(BorderStyle.THIN); // 上边框
		cellStyleCommon.setAlignment(HorizontalAlignment.LEFT);
		cellStyleCommon.setVerticalAlignment(VerticalAlignment.BOTTOM);

		HSSFFont txtFontCommon = (HSSFFont) wb.createFont();
		txtFontCommon.setFontName("Calibri");
		txtFontCommon.setFontHeightInPoints((short) 11);
		cellStyleCommon.setFont(txtFontCommon);
		cellStyleCommon.setWrapText(true);
		return cellStyleCommon;
	}

	private static CellStyle getTitleStyle(HSSFWorkbook wb) {
		CellStyle cellStyleTitle = wb.createCellStyle();                        //表头单元格样式
		cellStyleTitle.setBorderBottom(BorderStyle.THIN); // 下边框
		cellStyleTitle.setBorderLeft(BorderStyle.THIN); // 左边框
		cellStyleTitle.setBorderRight(BorderStyle.THIN); // 有边框
		cellStyleTitle.setBorderTop(BorderStyle.THIN); // 上边框
		cellStyleTitle.setAlignment(HorizontalAlignment.CENTER);
		cellStyleTitle.setVerticalAlignment(VerticalAlignment.BOTTOM);

		HSSFFont txtFontTitle = (HSSFFont) wb.createFont();
		txtFontTitle.setFontName("Arial Unicode MS");
		txtFontTitle.setFontHeightInPoints((short) 28);
		txtFontTitle.setBold(true);
		cellStyleTitle.setFont(txtFontTitle);
		return cellStyleTitle;
	}

	private static void borderCellRange(CellRangeAddress region, HSSFSheet sheet) {
		RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet); // 下边框
		RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet); // 左边框
		RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet); // 有边框
		RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet); // 上边框
	}
}
